﻿using System;
using System.Collections.Generic;
using System.Text;
using Daco.Web.Store;
using System.Data.SqlClient;
using System.Data;

namespace Daco.Web.Store.Providers
{
    using Daco.Data;
    using Daco.Web.Data;

    internal class CStoreProvider
    {
        private CDbAccess _db = null;

        private static CStoreProvider _instance = null;
        internal static CStoreProvider Instance
        {
            get
            {
                if (_instance == null)
                    _instance = new CStoreProvider();
                return _instance;
            }
        }

        protected CStoreProvider()
        {
            _db = new CDbAccess("Store", string.Empty);
            _db.SetConnectionManagerAdapter<CStoreProvider>();
        }

        #region Product Category
        internal int CreateCategory(string culture, string categoryTitle, string description)
        {
            return CreateCategory(0, categoryTitle, description);
        }
        internal int CreateCategory(int parentCategoryID, string categoryTitle, string description)
        {
            int categoryID = 0;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ProductCategories_CreateCategory"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ParentCategoryID", System.Data.SqlDbType.Int).Value = parentCategoryID;
            cmd.Parameters.Add("@CategoryTitle", System.Data.SqlDbType.NVarChar).Value = categoryTitle;
            cmd.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar).Value = description;
            cmd.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

            _db.ExecuteNonQuery(cmd);
            categoryID = Convert.ToInt32(cmd.Parameters["@CategoryID"].Value);

            return categoryID;
        }
        internal CProductCategory GetCategory(int categoryID)
        {
            CProductCategory category = null;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ProductCategories_GetCategory"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int).Value = categoryID;
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;

                System.Data.IDataReader reader = null;
                reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleRow);
                if (reader.Read())
                    category = GetProductCategoryFromReader(reader);
            }

            return category;
        }
        internal IEnumerable<CProductCategory> GetAllCategories()
        {
            return GetCategories(-1);
        }
        internal IEnumerable<CProductCategory> GetCategories(int parentCategoryID)
        {
            List<CProductCategory> categories = new List<CProductCategory>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ProductCategories_GetAllCategories"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ParentCategoryID", System.Data.SqlDbType.Int).Value = parentCategoryID;
            cmd.Parameters.Add("@ResultCount", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;

                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
                int resultCount = Convert.ToInt32(cmd.Parameters["@ResultCount"].Value);
                while (reader.Read())
                    categories.Add(GetProductCategoryFromReader(reader));
            }

            return categories;
        }
        internal IEnumerable<CProductCategory> GetAllCategories(int parentCategoryID)
        {
            List<CProductCategory> categories = new List<CProductCategory>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ProductCategories_GetAllCategories"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ParentCategoryID", System.Data.SqlDbType.Int).Value = parentCategoryID;
            cmd.Parameters.Add("@ResultCount", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
                int resultCount = Convert.ToInt32(cmd.Parameters["@ResultCount"].Value);
                while (reader.Read())
                    categories.Add(GetProductCategoryFromReader(reader));
            }

            return categories;
        }
        internal bool UpdateCategory(int categoryID, int parentCategoryID, string title, string description)
        {
            int affectedRows = 0;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("UpdateCategory"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@CategoryID", System.Data.SqlDbType.Int).Value = categoryID;
            cmd.Parameters.Add("@ParentCategoryID", System.Data.SqlDbType.Int).Value = parentCategoryID;
            cmd.Parameters.Add("@CategoryTitle", System.Data.SqlDbType.NVarChar, 256).Value = title;
            cmd.Parameters.Add("@Description", System.Data.SqlDbType.NVarChar, 500).Value = description;

            affectedRows = _db.ExecuteNonQuery(cmd);

            return affectedRows > 0;
        }
        internal bool DeleteCategory(int categoryID, bool throwOnPopulatedCategory)
        {
            return false;
        }
        #endregion

        #region Product
        internal CProduct GetProduct(string productID, bool updateViewCount)
        {
            CProduct product = null;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetProduct"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", System.Data.SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@UpdateViewCount", SqlDbType.Bit).Value = updateViewCount;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleRow);
                if (reader.Read())
                    product = GetProductFromReader(reader);
            }

            return product;
        }
        internal int AddProduct(DateTime addedDate, string productID, string productName, string productSeries, int manufatureID, string manufactureName, int pcategoryID, string pcategoryTitle,
            string unitMeasureCode, float unitPrice, bool enablePriceConfidential, bool enableShopping, string currency, int unitsInStock)
        {
            int retv = 0;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_AddProduct"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = addedDate;
            cmd.Parameters.Add("@ProductID", System.Data.SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar, 256).Value = productName;
            cmd.Parameters.Add("@ProductSeries", System.Data.SqlDbType.VarChar).Value = productSeries;
            cmd.Parameters.Add("@ManufactureID", System.Data.SqlDbType.Int).Value = manufatureID;
            cmd.Parameters.Add("@ManufactureName", System.Data.SqlDbType.NVarChar, 256).Value = manufactureName;
            cmd.Parameters.Add("@PCategoryID", SqlDbType.Int).Value = pcategoryID;
            cmd.Parameters.Add("@PCategoryTitle", SqlDbType.NVarChar, 256).Value = pcategoryTitle;
            cmd.Parameters.Add("@UnitMeasureCode", System.Data.SqlDbType.VarChar).Value = unitMeasureCode;
            cmd.Parameters.Add("@UnitPrice", System.Data.SqlDbType.Money).Value = unitPrice;
            cmd.Parameters.Add("@EnablePriceConfidential", SqlDbType.Bit).Value = enablePriceConfidential;
            cmd.Parameters.Add("@EnableShopping", SqlDbType.Bit).Value = enableShopping;
            cmd.Parameters.Add("@Currency", System.Data.SqlDbType.VarChar).Value = currency;
            cmd.Parameters.Add("@UnitsInStock", System.Data.SqlDbType.Int).Value = unitsInStock;
            cmd.Parameters.Add("@RETURN_VALUE", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.ReturnValue;

            _db.ExecuteNonQuery(cmd);
            retv = Convert.ToInt32(cmd.Parameters["@RETURN_VALUE"].Value);

            return retv;
        }

        internal bool UpdateProductDescription(string culture, int productCategoryID, string productCategoryTitle, string productID, string productName, string description)
        {
            bool success = false;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("UpdateProductDescription"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductCategoryID", SqlDbType.Int).Value = productCategoryID;
            cmd.Parameters.Add("@ProductCategoryTitle", SqlDbType.NVarChar, 256).Value = productCategoryTitle;
            cmd.Parameters.Add("@ProductID", System.Data.SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@ProductName", System.Data.SqlDbType.NVarChar).Value = productName;
            cmd.Parameters.Add("@Description", SqlDbType.NVarChar, int.MaxValue).Value = description;

            _db.ExecuteNonQuery(cmd);
            success = true;

            return success;
        }

        internal string AddProduct(string[] productSeries, int[] productCategoryID, int[] manufatureID
            , string[] unitMeasureCode, float[] UnitPrice, string currency, int[] quantity)
        {
            return string.Empty;
        }

        internal bool RemoveProduct(string productSeries)
        {
            return false;
        }
        internal bool RemoveProduct(string[] productSeries)
        {
            return false;
        }
        internal CProduct[] GetProducts(int pageIndex, int pageSize, out int resultCount)
        {
            List<CProduct> products = new List<CProduct>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetProducts"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@PageIndex", System.Data.SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", System.Data.SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@OUTPUT_VALUE", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
                while (reader.Read())
                    products.Add(GetProductFromReader(reader));

                reader.Close();

                // Reader must be closed before get OUTPUT_VALUE
                resultCount = Convert.ToInt32(cmd.Parameters["@OUTPUT_VALUE"].Value);
            }

            return products.ToArray();
        }
        internal CProduct[] GetProductsByCategory(int productCategoryID, int pageIndex, int pageSize, out int resultCount)
        {
            List<CProduct> products = new List<CProduct>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetProductsByCategory"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductCategoryID", System.Data.SqlDbType.Int).Value = productCategoryID;
            cmd.Parameters.Add("@PageIndex", System.Data.SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", System.Data.SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@OUTPUT_VALUE", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
                while (reader.Read())
                    products.Add(GetProductFromReader(reader));

                resultCount = Convert.ToInt32(cmd.Parameters["@OUTPUT_VALUE"].Value);

                reader.Close();
            }

            return products.ToArray();
        }
        internal CProduct[] GetFilterProducts(string productCategoryIDList, string manufactureIDList, string productSeries, string productName, float priceFrom, float priceTo, int pageIndex, int pageSize, out int resultCount)
        {
            List<CProduct> products = new List<CProduct>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetFilterProducts"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductCategoryIDList", SqlDbType.VarChar, 1000).Value = productCategoryIDList;
            cmd.Parameters.Add("@ManufactureIDList", SqlDbType.VarChar, 1000).Value = manufactureIDList;
            cmd.Parameters.Add("@ProductSeries", System.Data.SqlDbType.VarChar, 10).Value = productSeries;
            cmd.Parameters.Add("@ProductName", System.Data.SqlDbType.NVarChar, 256).Value = productName;
            cmd.Parameters.Add("@PriceFrom", SqlDbType.Float).Value = priceFrom;
            cmd.Parameters.Add("@PriceTo", SqlDbType.Float).Value = priceTo;
            cmd.Parameters.Add("@PageIndex", System.Data.SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", System.Data.SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@OUTPUT_VALUE", System.Data.SqlDbType.Int).Direction = System.Data.ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
                while (reader.Read())
                    products.Add(GetProductFromReader(reader));

                reader.Close();
                resultCount = cmd.Parameters["@OUTPUT_VALUE"].Value == DBNull.Value ? 0 : Convert.ToInt32(cmd.Parameters["@OUTPUT_VALUE"].Value);

                reader.Close();
            }

            return products.ToArray();
        }
        internal CProduct[] GetNewestProducts(int itemCount)
        {
            List<CProduct> productList = new List<CProduct>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetNewestProducts"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ItemCount", SqlDbType.Int).Value = itemCount;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;

                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                    productList.Add(GetProductFromReader(reader));
            }

            return productList.ToArray();
        }

        internal CProduct[] GetNewestProductsByCategory(out int resultCount)
        {
            List<CProduct> productList = new List<CProduct>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetNewestProductsByCategory"));
            cmd.CommandType = CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;

                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                    productList.Add(GetProductFromReader(reader));

                resultCount = productList.Count;
            }

            return productList.ToArray();
        }

        internal CProduct[] GetFavoriteProducts(int itemCount)
        {
            List<CProduct> productList = new List<CProduct>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetFavoriteProducts"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ItemCount", SqlDbType.Int).Value = itemCount;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;

                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                    productList.Add(GetProductFromReader(reader));
            }

            return productList.ToArray();
        }
        #endregion

        #region Product Photo
        internal bool AddProductPhoto(string productPhotoID, string thumbnailPhotoFileName, byte[] thumbnailPhoto, string largePhotoFileName, byte[] largePhoto)
        {
            bool success = false;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("AddPhoto"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductPhotoID", System.Data.SqlDbType.VarChar).Value = productPhotoID;
            cmd.Parameters.Add("@ThumbnailPhotoFileName", System.Data.SqlDbType.NVarChar).Value = thumbnailPhotoFileName;
            cmd.Parameters.Add("@ThumbnailPhoto", System.Data.SqlDbType.Binary).Value = thumbnailPhoto;
            cmd.Parameters.Add("@LargePhotoFileName", System.Data.SqlDbType.NVarChar).Value = largePhotoFileName;
            cmd.Parameters.Add("@LargePhoto", System.Data.SqlDbType.Binary).Value = largePhoto;

            _db.ExecuteNonQuery(cmd);
            success = true;

            return success;
        }
        internal bool AddProductPhoto(string productID, string productPhotoID, string thumbnailPhotoFileName, byte[] thumbnailPhoto, string largePhotoFileName, byte[] largePhoto, bool primary)
        {
            bool success = false;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("AddProductPhoto"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", System.Data.SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@ProductPhotoID", System.Data.SqlDbType.VarChar).Value = productPhotoID;
            cmd.Parameters.Add("@ThumbnailPhotoFileName", System.Data.SqlDbType.NVarChar, 256).Value = thumbnailPhotoFileName;
            cmd.Parameters.Add("@ThumbnailPhoto", System.Data.SqlDbType.VarBinary, int.MaxValue).Value = thumbnailPhoto;
            cmd.Parameters.Add("@LargePhotoFileName", System.Data.SqlDbType.NVarChar, 256).Value = largePhotoFileName;
            cmd.Parameters.Add("@LargePhoto", System.Data.SqlDbType.VarBinary, int.MaxValue).Value = largePhoto;
            cmd.Parameters.Add("@Primary", System.Data.SqlDbType.Bit).Value = primary;

            _db.ExecuteNonQuery(cmd);
            success = true;

            return success;
        }
        internal byte[] GetProductPhoto(string productPhotoID, bool largePhoto)
        {
            byte[] content = null;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetProductPhoto"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductPhotoID", System.Data.SqlDbType.VarChar, 8).Value = productPhotoID;
            cmd.Parameters.Add("@LargePhoto", System.Data.SqlDbType.Bit).Value = largePhoto;

            object retv = _db.ExecuteSalar(cmd);
            if (retv != null)
            {
                try
                {
                    content = (byte[])retv;
                }
                catch (Exception) { }
            }

            return content;
        }
        #endregion

        #region Common
        internal System.Data.DataTable GetUnitMeasureTable()
        {
            System.Data.DataTable tbl = new System.Data.DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetUnitMeasureTable"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);

                tbl.Load(reader);

                reader.Close();
            }

            return tbl;
        }

        internal System.Data.DataTable GetManufactureTable()
        {
            System.Data.DataTable tbl = new System.Data.DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ProductManufactures_GetManufactureTable"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);

                tbl.Load(reader);

                reader.Close();
            }

            return tbl;
        }
        internal System.Data.DataTable GetCurrencyTable()
        {
            System.Data.DataTable tbl = new System.Data.DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetCurrencyTable"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
                tbl.Load(reader);

                reader.Close();
            }

            return tbl;
        }
        internal string AddProductSpecialization(string attachmentID, int productCategoryID, string title, string fileName, string fileExtension, string fileType, byte[] fileContent)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("AddProductSpecialization"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@AttachmentID", System.Data.SqlDbType.VarChar, 8).Value = attachmentID;
            cmd.Parameters.Add("@ProductCategoryID", System.Data.SqlDbType.Int).Value = productCategoryID;
            cmd.Parameters.Add("@Title", System.Data.SqlDbType.NVarChar, 256).Value = title;
            cmd.Parameters.Add("@FileName", System.Data.SqlDbType.NVarChar, 256).Value = fileName;
            cmd.Parameters.Add("@FileExtension", System.Data.SqlDbType.VarChar, 6).Value = fileExtension;
            cmd.Parameters.Add("@ContentType", System.Data.SqlDbType.VarChar, 50).Value = fileType;
            cmd.Parameters.Add("@Content", System.Data.SqlDbType.VarBinary, int.MaxValue).Value = fileContent;

            int retv = _db.ExecuteNonQuery(cmd);

            return attachmentID;
        }
        #endregion

        #region Virtual Methods
        virtual protected CProduct GetProductFromReader(System.Data.IDataReader reader)
        {
            CProduct product = new CProduct
            (
                reader["ProductID"].ToString(),
                Convert.ToDateTime(reader["AddedDate"]),
                reader["ProductSeries"].ToString(),
                reader["ProductName"].ToString(),
                Convert.ToSingle(reader["UnitPrice"]),
                reader["UnitMeasureCode"].ToString(),
                reader["UnitMeasure"].ToString(),
                reader["Currency"].ToString(),
                Convert.ToInt32(reader["UnitsInStock"]),
                Convert.ToInt32(reader["TotalRating"]),
                Convert.ToInt32(reader["VoteCount"])
            );

            try
            {
                product.EnableShopping = Convert.ToBoolean(reader["EnableShopping"]);
                product.EnablePriceConfidential = Convert.ToBoolean(reader["EnablePriceConfidential"]);
            }
            catch
            {
                product.EnableShopping = true;
                product.EnablePriceConfidential = true;
            }

            product.RefCategory
                (
                    new CProductCategory
                    (
                        Convert.ToInt32(reader["CategoryID"]),
                        reader["CategoryTitle"].ToString(),
                        string.Empty
                    )
                );

            product.Manufacture = 
                    new CManufacture
                    (
                        Convert.ToInt32(reader["ManufactureID"]),
                        reader["ManufactureName"].ToString()
                    );

            return product;
        }
        virtual protected CProductCategory GetProductCategoryFromReader(System.Data.IDataReader reader)
        {
            return new CProductCategory
                (
                    Convert.ToInt32(reader["PCategoryID"]),
                    Convert.ToInt32(reader["PParentCategoryID"]),
                    reader["PParentCategoryTitle"].ToString(),
                    reader["PCategoryTitle"].ToString(),
                    reader["Description"].ToString()
                );
        }
        #endregion

        internal CProductReview[] GetProductReviews(string productID, bool approved, int pageIndex, int pageSize, out int resultCount)
        {
            List<CProductReview> productReviews = new List<CProductReview>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetProductReviews"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", System.Data.SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@Approved", System.Data.SqlDbType.Bit).Value = approved;
            cmd.Parameters.Add("@PageIndex", System.Data.SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", System.Data.SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@RESULT_COUNT", SqlDbType.Int).Direction = ParameterDirection.Output;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                System.Data.IDataReader reader = _db.ExecuteReader(cmd, System.Data.CommandBehavior.SingleResult);
                while (reader.Read())
                {
                    productReviews.Add
                        (
                            new CProductReview
                            (
                                Convert.ToInt32(reader["ReviewID"]),
                                Convert.ToBoolean(reader["IsApproved"]),
                                Convert.ToDateTime(reader["AddedDate"]),
                                reader["ReviewerName"].ToString(),
                                reader["ReviewerEmail"].ToString(),
                                reader["IPAddress"].ToString(),
                                Convert.ToInt32(reader["Rating"]),
                                reader["Comments"].ToString()
                            )
                        );
                }

                resultCount = Convert.ToInt32(cmd.Parameters["@RESULT_COUNT"].Value);
            }

            return productReviews.ToArray();
        }
        internal int GetProductReviewCount(string productID, bool approved)
        {
            int resultCount = 0;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("GetProductReivewCount"));
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", System.Data.SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@Approved", System.Data.SqlDbType.Bit).Value = approved;

            resultCount = Convert.ToInt32(_db.ExecuteSalar(cmd));

            return resultCount;
        }

        internal bool DeletePhoto(string photoID)
        {
            int retv = 0;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Photos_DeletePhoto"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@PhotoID", SqlDbType.VarChar, 8).Value = photoID;

            retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal string AddPhoto(string photoID, string productID, string title, string thumbFileName, byte[] thumbPhoto, string largeFileName, byte[] largePhoto, bool primary)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Photos_AddPhoto"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@PhotoID", SqlDbType.VarChar, 8).Value = photoID;
            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 8).Value = productID;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 256).Value = title;
            cmd.Parameters.Add("@ThumbPhotoFileName", SqlDbType.NVarChar, 256).Value = thumbFileName;
            cmd.Parameters.Add("@ThumbPhoto", SqlDbType.VarBinary, int.MaxValue).Value = thumbPhoto;
            cmd.Parameters.Add("@LargePhotoFileName", SqlDbType.NVarChar, 256).Value = largeFileName;
            cmd.Parameters.Add("@LargePhoto", SqlDbType.VarBinary, int.MaxValue).Value = largePhoto;
            cmd.Parameters.Add("@Primary", SqlDbType.Bit).Value = primary;

            if (_db.ExecuteNonQuery(cmd) == 0)
            {
                photoID = string.Empty;
            }

            return photoID;
        }

        internal byte[] GetPhotoContent(string photoID, bool largePhoto)
        {
            byte[] content = null;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Photos_GetPhotoContent"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@PhotoID", SqlDbType.VarChar, 8).Value = photoID;
            cmd.Parameters.Add("@IsLargePhoto", SqlDbType.Bit).Value = largePhoto;

            object o = _db.ExecuteSalar(cmd);
            if (o != null)
                content = (byte[])o;

            return content;
        }

        internal IEnumerable<CPhoto> GetPhotos(string productID)
        {
            List<CPhoto> photos = new List<CPhoto>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Photos_GetPhotos"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 8).Value = productID;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                    photos.Add(GetPhotoFromReader(reader));

                reader.Close();
                reader.Dispose();
            }

            return photos;
        }

        private CPhoto GetPhotoFromReader(IDataReader reader)
        {
            return new CPhoto
            (
                reader["PhotoID"].ToString(),
                reader["Title"].ToString(),
                reader["ThumbnailPhotoFileName"].ToString(),
                reader["LargePhotoFileName"].ToString(),
                Convert.ToBoolean(reader["Primary"])
            );
        }

        internal void IncrementViewCount(string productID)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_IncrementViewCount"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 15).Value = productID;

            int retv = _db.ExecuteNonQuery(cmd);
        }

        internal void RateProduct(string productID, int rating)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_RateProduct"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@Rating", SqlDbType.Int).Value = rating;

            int retv = _db.ExecuteNonQuery(cmd);
        }

        internal CProduct[] GetRelatedProducts(string productID, int categoryID, int manufactureID, int itemCount)
        {
            List<CProduct> productList = new List<CProduct>();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetRelatedProducts"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@PCategoryID", SqlDbType.Int).Value = categoryID;
            cmd.Parameters.Add("@ManufactureID", SqlDbType.Int).Value = manufactureID;
            cmd.Parameters.Add("@ItemCount", SqlDbType.Int).Value = itemCount;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;

                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                    productList.Add(GetProductFromReader(reader));
            }

            return productList.ToArray();
        }

        internal DataRow GetProductDescription(int descriptionID)
        {
            DataTable tblDescription = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetProductDescription"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductDescriptionID", SqlDbType.Int).Value = descriptionID;
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleRow);
                tblDescription.Load(reader, LoadOption.Upsert);
            }

            return tblDescription.Rows.Count > 0 ? tblDescription.Rows[0] : null;
        }

        internal bool ApproveReview(int productReviewID)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ApproveReview"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductReviewID", SqlDbType.Int).Value = productReviewID;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal bool UpdateProductDetail(string productID, int manufactureID, string manufactureName, string productSeries, float unitPrice, string currency, string unitMeasureCode, int unitsInStock, bool enablePriceConfidential, bool enableShopping)
        {
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_UpdateProductDetail"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 15).Value = productID;
            cmd.Parameters.Add("@ManufactureID", SqlDbType.Int).Value = manufactureID;
            cmd.Parameters.Add("@ManufactureName", SqlDbType.NVarChar, 256).Value = manufactureName;
            cmd.Parameters.Add("@ProductSeries", SqlDbType.VarChar, 256).Value = productSeries;
            cmd.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = unitPrice;
            cmd.Parameters.Add("@Currency", SqlDbType.VarChar, 3).Value = currency;
            cmd.Parameters.Add("@UnitMeasureCode", SqlDbType.VarChar, 3).Value = unitMeasureCode;
            cmd.Parameters.Add("@UnitsInStock", SqlDbType.Int).Value = unitsInStock;
            cmd.Parameters.Add("@EnablePriceConfidential", SqlDbType.Bit).Value = enablePriceConfidential;
            cmd.Parameters.Add("@EnableShopping", SqlDbType.Int).Value = enableShopping;

            int retv = _db.ExecuteNonQuery(cmd);

            return retv > 0;
        }

        internal string[] GetFullProductCategoryTitle(int productCategoryID)
        {
            List<string> categoryTitles = new List<string>();
            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ProductCategories_GetFullTitle"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ProductCategoryID", SqlDbType.Int).Value = productCategoryID;
            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                while (reader.Read())
                    categoryTitles.Add(reader["Title"].ToString());
                reader.Close();
            }
            return categoryTitles.ToArray();
        }

        internal DataTable GetManufacture(int manufactureID)
        {
            DataTable tblManufacture = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("ProductManufactures_GetManufacture"));
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@ManufactureID", SqlDbType.Int).Value = manufactureID;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleRow);
                tblManufacture.Load(reader, LoadOption.Upsert);
                reader.Close();
            }

            return tblManufacture;
        }

        internal DataTable GetProductDescriptions(string productID)
        {
            DataTable tblDescription = new DataTable();

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_GetProductDescriptions"));
            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 15).Value = productID;
            cmd.CommandType = CommandType.StoredProcedure;

            using (CDbConnectionManager m = _db.GetConnectionManager())
            {
                cmd.Connection = m.OpenDBConnection;
                IDataReader reader = _db.ExecuteReader(cmd, CommandBehavior.SingleResult);
                tblDescription.Load(reader, LoadOption.Upsert);
                reader.Close();
            }

            return tblDescription;
        }

        internal bool AddProductDescription(string productID, string title, string description)
        {
            int descriptionID = -1;

            SqlCommand cmd = new SqlCommand(_db.GetObjectName("Products_AddDescription"));
            cmd.Parameters.Add("@ProductID", SqlDbType.VarChar, 8).Value = productID;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar, 256).Value = title;
            cmd.Parameters.Add("@Description", SqlDbType.NVarChar, int.MaxValue).Value = description;
            cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            cmd.CommandType = CommandType.StoredProcedure;

            int retv = _db.ExecuteNonQuery(cmd);
            descriptionID = (int)cmd.Parameters["@RETURN_VALUE"].Value;

            return descriptionID > 0;
        }
    }
}
